package com.txtsqlclient.dataaccess.database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.txtsqlclient.dataaccess.DAOException;
import com.txtsqlclient.domain.QueryResult;
import com.txtsqlclient.domain.ReadQueryResult;
import com.txtsqlclient.domain.WriteQueryResult;

/**
 * This class is required to execute various SQL queries using the database
 * connection.
 * 
 * @author spadda
 */
public class QueryExecutionDAO {

	private static QueryExecutionDAO instance;

	private static Log LOGGER = LogFactory.getLog(QueryExecutionDAO.class);

	protected QueryExecutionDAO() {
		// empty constructor to enforce access restrictions.
	}

	public static QueryExecutionDAO getInstance() {
		if (instance == null) {
			LOGGER
					.debug("Factory instance does not exist. Creating a new instance.");
			instance = new QueryExecutionDAO();
		}
		return instance;
	}

	/**
	 * This method is required to execute a database query.
	 * 
	 * @param query
	 * @return a list of lists of strings, representing the table of the
	 *         results.
	 * @throws DAOException
	 */
	public QueryResult execute(final String query, final Connection connection)
			throws DAOException {
		QueryResult result = null;
		Statement stmt = null;
		try {
			stmt = connection.createStatement();
			boolean isReadQuery = stmt.execute(query);
			if (LOGGER.isDebugEnabled()) {
				LOGGER.debug("Executed " + (isReadQuery ? "READ" : "WRITE")
						+ " query[" + query+"]");
			}
			if (isReadQuery) {
				result = getReadQueryResult(stmt);
			} else {
				result = getWriteQueryResult(stmt);
			}
		} catch (SQLException e) {
			throw new DAOException("Error while executing query:" + query, e);
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					throw new DAOException("Error closing result set", e);
				}
			}
		}
		LOGGER.trace("Query execution complete");
		return result;
	}

	protected QueryResult getReadQueryResult(final Statement stmt)
			throws SQLException {
		final List<List<String>> resultData = new ArrayList<List<String>>();
		final ResultSet rs = stmt.getResultSet();
		final ResultSetMetaData rsMetaData = rs.getMetaData();
		int numOfCols = rsMetaData.getColumnCount();
		final List<String> header = new ArrayList<String>();
		for (int i = 0; i < numOfCols; i++) {
			header.add(rsMetaData.getColumnName(i + 1));
		}

		int numOfRowsReturned = 0;
		while (rs.next()) {
			List<String> dataRow = new ArrayList<String>();
			for (int i = 0; i < numOfCols; i++) {
				dataRow.add(rs.getString(i + 1));
			}
			resultData.add(dataRow);
			numOfRowsReturned++;
		}
		LOGGER.debug("Query result contains " + numOfRowsReturned
				+ " rows and " + numOfCols + " columns");
		final QueryResult result = new ReadQueryResult(header, resultData);
		return result;
	}

	protected QueryResult getWriteQueryResult(final Statement stmt)
			throws SQLException {
		final int numOfRowsModified = stmt.getUpdateCount();
		final QueryResult result = new WriteQueryResult(numOfRowsModified);
		return result;
	}

}